from pymysql import Connection
from datetime import datetime

from day15.card import Card

conn = None

# 创建数据库连接独享
def Connect():
    conn = Connection(
        host='localhost',
        port=3306,
        user='root',
        passwd='root',
        database='db3',
        charset='utf8',
        autocommit=True
    )
    return conn

def closeDB():
    if conn:
        conn.close()

# 创建数据表并初始化
def createTable():
    conn = Connect()
    cursor = conn.cursor()

    # 创建数据表
    create_rank_sql = """
        create table if not exists t_rank(
            id int primary key auto_increment,
            r_name varchar(20)
        )
    """

    create_card_sql = """
        create table if not exists t_card(
            id int primary key auto_increment,
            username varchar(20),
            card_no  varchar(20),
            create_time datetime,
            money double,
            r_id int
        )
    """

    cursor.execute(create_rank_sql)  # 创建银行
    cursor.execute(create_card_sql)  # 创建银行卡

    # 初始化银行
    ranks = ['工商银行','建设银行','农业银行','中国银行','招商银行']
    for rank in ranks:
        add_rank_sql = """
            insert into t_rank values(null,%s)
        """
        # 执行SQL
        cursor.execute(add_rank_sql,rank)

    conn.close()

def add_card(card):
    conn = Connect()
    cursor = conn.cursor()


    sql = """
        insert into t_card values(null,%s,%s,%s,%s,%s)
    """
    # 处理日期格式
    create_time = card.createTime.strftime('%Y-%m-%d %H:%M:%S')
    print(create_time)
    cursor.execute(sql,(card.username,card.cardNo,create_time,card.money,card.rId))

    print('添加成功')
    conn.close()

def findAll():
    conn = Connect()
    cursor = conn.cursor()

    sql = """
       select * from t_card
          """
    cursor.execute(sql)
    result = cursor.fetchall()
    for row in result:
        for item in row:
            print(item, end = '\t')
        print()
    conn.close()

if __name__ == '__main__':
    # c1 = Card('刘千琪','yl123456',datetime.now(),1000,1)
    # add_card(c1)
    findAll()